﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class ModeloContabilData
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        If Not Page.IsPostBack Then

            Try
                FillDDL()

                If (Session("strOperacao") = "C" Or Session("strOperacao") = "A" Or Session("strOperacao") = "E") Then ' Consultar | Alterar | Excluir
                    LoadData()
                End If

                If (Session("strOperacao") = "C") Then ' Consultar
                    btnConfirmar.Enabled = False
                End If

            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End If

    End Sub

    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaHolding, CodigoDaHolding + ' - ' + NomeDaHolding AS Holding" & _
                     " FROM Holding" & _
                     " ORDER BY CodigoDaHolding;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlHolding.DataTextField = "Holding"
                ddlHolding.DataValueField = "CodigoDaHolding"
                ddlHolding.DataSource = mydatatable
                ddlHolding.DataBind()

                ddlHolding.Items.Insert(0, "")
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaUF, SiglaDaUF" & vbNewLine & _
                         " FROM UF" & vbNewLine & _
                         " ORDER BY SiglaDaUF;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlUFOrigem.DataTextField = "SiglaDaUF"
                ddlUFOrigem.DataValueField = "CodigoDaUF"
                ddlUFOrigem.DataSource = mydatatable
                ddlUFOrigem.DataBind()

                ddlUFOrigem.Items.Insert(0, "")

                ddlUFDestino.DataTextField = "SiglaDaUF"
                ddlUFDestino.DataValueField = "CodigoDaUF"
                ddlUFDestino.DataSource = mydatatable
                ddlUFDestino.DataBind()

                ddlUFDestino.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodNaturezaOperacao," & _
                     " CodNaturezaOperacao + ' - ' + DscNaturezaOperacao AS NaturezaOperacao" & vbNewLine & _
                     " FROM NaturezaOperacao" & _
                     " WHERE CodTipoOperacao = 'E'" & _
                     " ORDER BY CodNaturezaOperacao;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlNaturezaOperacao.DataTextField = "NaturezaOperacao"
                ddlNaturezaOperacao.DataValueField = "CodNaturezaOperacao"
                ddlNaturezaOperacao.DataSource = mydatatable
                ddlNaturezaOperacao.DataBind()

                ddlNaturezaOperacao.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodCategoriaFiscal, DscCategoriaFiscal" & _
                         " FROM CategoriaFiscal" & _
                         " ORDER BY DscCategoriaFiscal;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCategoriaFiscal.DataTextField = "DscCategoriaFiscal"
                ddlCategoriaFiscal.DataValueField = "CodCategoriaFiscal"
                ddlCategoriaFiscal.DataSource = mydatatable
                ddlCategoriaFiscal.DataBind()

                ddlCategoriaFiscal.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodMeioTransporte, DscMeioTransporte" & _
                         " FROM MeioTransporte" & _
                         " ORDER BY DscMeioTransporte;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlMeioTransporte.DataTextField = "DscMeioTransporte"
                ddlMeioTransporte.DataValueField = "CodMeioTransporte"
                ddlMeioTransporte.DataSource = mydatatable
                ddlMeioTransporte.DataBind()

                ddlMeioTransporte.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaOrigemDoItem, DescricaoDaOrigemDoItem" & _
                         " FROM OrigemDoItem" & _
                         " ORDER BY CodigoDaOrigemDoItem;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlOrigemItem.DataTextField = "DescricaoDaOrigemDoItem"
                ddlOrigemItem.DataValueField = "CodigoDaOrigemDoItem"
                ddlOrigemItem.DataSource = mydatatable
                ddlOrigemItem.DataBind()

                ddlOrigemItem.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodDestinacaoItem, DscDestinacaoItem" & _
                         " FROM DestinacaoItem" & _
                         " ORDER BY DscDestinacaoItem;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlDestinacaoItem.DataTextField = "DscDestinacaoItem"
                ddlDestinacaoItem.DataValueField = "CodDestinacaoItem"
                ddlDestinacaoItem.DataSource = mydatatable
                ddlDestinacaoItem.DataBind()

                ddlDestinacaoItem.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao + ' - ' + DscTipoTributacao AS TipoTributacao" & _
                         " FROM TipoTributacao" & _
                         " ORDER BY CodTipoTributacao;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlTipoTributacaoII.DataTextField = "TipoTributacao"
                ddlTipoTributacaoII.DataValueField = "CodTipoTributacao"
                ddlTipoTributacaoII.DataSource = mydatatable
                ddlTipoTributacaoII.DataBind()

                ddlTipoTributacaoII.Items.Insert(0, "")

                ddlTipoTributacaoIPI.DataTextField = "TipoTributacao"
                ddlTipoTributacaoIPI.DataValueField = "CodTipoTributacao"
                ddlTipoTributacaoIPI.DataSource = mydatatable
                ddlTipoTributacaoIPI.DataBind()

                ddlTipoTributacaoIPI.Items.Insert(0, "")

                ddlTipoTributacaoPIS.DataTextField = "TipoTributacao"
                ddlTipoTributacaoPIS.DataValueField = "CodTipoTributacao"
                ddlTipoTributacaoPIS.DataSource = mydatatable
                ddlTipoTributacaoPIS.DataBind()

                ddlTipoTributacaoPIS.Items.Insert(0, "")

                ddlTipoTributacaoCOFINS.DataTextField = "TipoTributacao"
                ddlTipoTributacaoCOFINS.DataValueField = "CodTipoTributacao"
                ddlTipoTributacaoCOFINS.DataSource = mydatatable
                ddlTipoTributacaoCOFINS.DataBind()

                ddlTipoTributacaoCOFINS.Items.Insert(0, "")

                ddlTipoTributacaoICMS.DataTextField = "TipoTributacao"
                ddlTipoTributacaoICMS.DataValueField = "CodTipoTributacao"
                ddlTipoTributacaoICMS.DataSource = mydatatable
                ddlTipoTributacaoICMS.DataBind()

                ddlTipoTributacaoICMS.Items.Insert(0, "")

                ddlTipoTributacaoICMSST.DataTextField = "TipoTributacao"
                ddlTipoTributacaoICMSST.DataValueField = "CodTipoTributacao"
                ddlTipoTributacaoICMSST.DataSource = mydatatable
                ddlTipoTributacaoICMSST.DataBind()

                ddlTipoTributacaoICMSST.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub


    Private Sub LoadData()

        Dim strDataVigencia As String

        ddlHolding.Enabled = False
        ddlCompanhia.Enabled = False
        ddlUFOrigem.Enabled = False
        ddlUFDestino.Enabled = False
        ddlNaturezaOperacao.Enabled = False
        ddlCategoriaFiscal.Enabled = False
        ddlMeioTransporte.Enabled = False
        ddlOrigemItem.Enabled = False
        ddlDestinacaoItem.Enabled = False

        txtDataVigencia.Enabled = False

        ddlTipoTributacaoII.Enabled = False
        ddlTipoTributacaoIPI.Enabled = False
        ddlTipoTributacaoPIS.Enabled = False
        ddlTipoTributacaoCOFINS.Enabled = False
        ddlTipoTributacaoICMS.Enabled = False
        ddlTipoTributacaoICMSST.Enabled = False


        If (Session("strOperacao").ToString = "C" Or Session("strOperacao").ToString = "E") Then ' Consultar | Excluir

        End If

        ddlHolding.SelectedValue = Session("strCodigoDaHolding").ToString
        FillDDLCompanhia()
        ddlCompanhia.SelectedValue = Session("strCodigoDaCia").ToString

        ddlNaturezaOperacao.SelectedValue = Session("strCodNaturezaOperacao").ToString

        txtDataVigencia.Text = Session("strDataVigencia").ToString

        strDataVigencia = "'" & FormatMediumDate(txtDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT" & _
                     " A.CodUFOrigem," & _
                     " A.CodUFDestino," & _
                     " A.CodCategoriaFiscal," & _
                     " A.CodMeioTransporte," & _
                     " A.CodDestinacaoItem," & _
                     " A.CodigoDaOrigemDoItem," & _
                     " A.DataVigencia,"

            theQueryString = theQueryString & _
                     " A.CodTipoTributacaoII," & _
                     " A.CodTipoTributacaoIPI," & _
                     " A.CodTipoTributacaoPIS," & _
                     " A.CodTipoTributacaoCOFINS," & _
                     " A.CodTipoTributacaoICMS," & _
                     " A.CodTipoTributacaoICMSST"


            theQueryString = theQueryString & _
                     " FROM ModeloContabilHeader A, uf B, uf C, categoriafiscal D, meiotransporte E, destinacaoitem F" & _
                     " WHERE A.CodUFOrigem = B.CodigoDaUF AND A.CodUFDestino = C.CodigoDaUF AND A.CodCategoriaFiscal = D.CodCategoriaFiscal AND A.CodMeioTransporte = E.CodMeioTransporte AND A.CodDestinacaoItem = F.CodDestinacaoItem AND A.CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
                     " AND A.CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND B.SiglaDaUF = '" & Session("strSiglaDaUFOrigem").ToString & "'" & _
                     " AND C.SiglaDaUF = '" & Session("strSiglaDaUFDestino").ToString & "'" & _
                     " AND A.CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "'" & _
                     " AND D.DscCategoriaFiscal = '" & Session("strDscCategoriaFiscal").ToString & "'" & _
                     " AND E.DscMeioTransporte = '" & Session("strDscMeioTransporte").ToString & "'" & _
                     " AND F.DscDestinacaoItem = '" & Session("strDscDestinacaoItem").ToString & "'" & _
                     " AND A.CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "'" & _
                     " AND A.DataVigencia = " & strDataVigencia & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    ddlUFOrigem.SelectedValue = theSqlDataReader!CodUFOrigem
                    ddlUFDestino.SelectedValue = theSqlDataReader!CodUFDestino
                    ddlCategoriaFiscal.SelectedValue = theSqlDataReader!CodCategoriaFiscal
                    ddlMeioTransporte.SelectedValue = theSqlDataReader!CodMeioTransporte
                    ddlDestinacaoItem.SelectedValue = theSqlDataReader!CodDestinacaoItem
                    ddlOrigemItem.SelectedValue = theSqlDataReader!CodigoDaOrigemDoItem

                    ddlTipoTributacaoII.SelectedValue = theSqlDataReader!CodTipoTributacaoII
                    ddlTipoTributacaoIPI.SelectedValue = theSqlDataReader!CodTipoTributacaoIPI
                    ddlTipoTributacaoPIS.SelectedValue = theSqlDataReader!CodTipoTributacaoPIS
                    ddlTipoTributacaoCOFINS.SelectedValue = theSqlDataReader!CodTipoTributacaoCOFINS
                    ddlTipoTributacaoICMS.SelectedValue = theSqlDataReader!CodTipoTributacaoICMS
                    ddlTipoTributacaoICMSST.SelectedValue = theSqlDataReader!CodTipoTributacaoICMSST

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Sub

    Protected Sub ddlHolding_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlHolding.SelectedIndexChanged

        FillDDLCompanhia()

    End Sub

    Private Sub FillDDLCompanhia()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaCia, CodigoDaCia + ' - ' + NomeDaCia AS Cia" & _
                     " FROM Companhia" & _
                     " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
                     " ORDER BY CodigoDaCia;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                ddlCompanhia.DataTextField = "Cia"
                ddlCompanhia.DataValueField = "CodigoDaCia"
                ddlCompanhia.DataSource = mydatatable
                ddlCompanhia.DataBind()

                ddlCompanhia.Items.Insert(0, "")

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Protected Sub btnConfirmar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConfirmar.Click

        Dim ConnectionTest As String
        ConnectionTest = "A"

        lblMsg.ForeColor = Drawing.Color.Blue
        lblMsg.Text = ""


        If (Session("strOperacao") = "I") Then ' Incluir

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If (CodeExist(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (AddRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "A") Then ' Alterar

            If Not (DataValid() = True) Then
                Exit Sub
            End If

            If Not (EditRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        ElseIf (Session("strOperacao") = "E") Then ' Excluir

            If (VerifyData(ConnectionTest) = True) Then
                Exit Sub
            End If

            If Not (DeleteRecord(ConnectionTest) = True) Then
                Exit Sub
            End If

        End If

        lblMsg.ForeColor = Drawing.Color.Green
        lblMsg.Text = "Operação realizada com sucesso. Clique no botão 'OK'"

        btnConfirmar.Enabled = False
        btnVoltar.Text = "OK"

    End Sub


    Private Function DataValid() As Boolean

        Dim strModeloAtual As String
        Dim strModeloProposto As String

        DataValid = False

        If (ddlHolding.Text = "") Then
            lblMsg.Text = "Holding deve ser selecionada."
            Exit Function
        End If

        If (ddlCompanhia.Text = "") Then
            lblMsg.Text = "Companhia deve ser selecionada."
            Exit Function
        End If

        If (ddlUFOrigem.Text = "") Then
            lblMsg.Text = "UF de origem deve ser selecionada."
            Exit Function
        End If

        If (ddlUFDestino.Text = "") Then
            lblMsg.Text = "UF de destino deve ser selecionada."
            Exit Function
        End If

        If (ddlNaturezaOperacao.Text = "") Then
            lblMsg.Text = "Natureza da Operação deve ser selecionada."
            Exit Function
        End If

        If (ddlCategoriaFiscal.Text = "") Then
            lblMsg.Text = "Categoria Fiscal deve ser selecionada."
            Exit Function
        End If

        If (ddlMeioTransporte.Text = "") Then
            lblMsg.Text = "Meio de Transporte deve ser selecionado."
            Exit Function
        End If

        If (ddlOrigemItem.Text = "") Then
            lblMsg.Text = "Origem do Item deve ser selecionada."
            Exit Function
        End If

        If (ddlDestinacaoItem.Text = "") Then
            lblMsg.Text = "Destinação do Item deve ser selecionada."
            Exit Function
        End If

        If (Trim(txtDataVigencia.Text) = "") Then
            lblMsg.Text = "Data de Vigência deve ser preenchida."
            Exit Function
        End If

        If Not IsDate(txtDataVigencia.Text) = True Then
            lblMsg.Text = "Data de Vigência inválida."
            Exit Function
        End If

        If (ddlTipoTributacaoII.Text = "") Then
            lblMsg.Text = "Tributação II (Modelo atual) deve ser selecionada."
            Exit Function
        End If

        If (ddlTipoTributacaoIPI.Text = "") Then
            lblMsg.Text = "Tributação IPI (Modelo atual) deve ser selecionada."
            Exit Function
        End If

        If (ddlTipoTributacaoPIS.Text = "") Then
            lblMsg.Text = "Tributação PIS (Modelo atual) deve ser selecionada."
            Exit Function
        End If

        If (ddlTipoTributacaoCOFINS.Text = "") Then
            lblMsg.Text = "Tributação COFINS (Modelo atual) deve ser selecionada."
            Exit Function
        End If

        If (ddlTipoTributacaoICMS.Text = "") Then
            lblMsg.Text = "Tributação ICMS (Modelo atual) deve ser selecionada."
            Exit Function
        End If

        If (ddlTipoTributacaoICMSST.Text = "") Then
            lblMsg.Text = "Tributação ICMS Substituto (Modelo atual) deve ser selecionada."
            Exit Function
        End If

        strModeloAtual = Left(ddlTipoTributacaoII.Text, 1) & Left(ddlTipoTributacaoIPI.Text, 1)
        strModeloAtual = strModeloAtual & Left(ddlTipoTributacaoPIS.Text, 1) & Left(ddlTipoTributacaoCOFINS.Text, 1)
        strModeloAtual = strModeloAtual & Left(ddlTipoTributacaoICMS.Text, 1) & Left(ddlTipoTributacaoICMSST.Text, 1)

        'If (strModeloAtual = strModeloProposto) Then
        '    lblMsg.Text = "Modelo proposto deve ser diferente do Modelo atual."
        '    Exit Function
        'End If

        DataValid = True

    End Function

    Private Function CodeExist(ByVal strConn As String) As Boolean

        Dim strDataVigencia As String

        CodeExist = False

        strDataVigencia = "'" & FormatMediumDate(txtDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "SELECT DataVigencia" & _
                     " FROM ModeloTributarioHeader" & _
                     " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodUFOrigem = " & ddlUFOrigem.SelectedValue & _
                     " AND CodUFDestino = " & ddlUFDestino.SelectedValue & _
                     " AND CodNaturezaOperacao = '" & ddlNaturezaOperacao.SelectedValue & "'" & _
                     " AND CodCategoriaFiscal = " & ddlCategoriaFiscal.SelectedValue & _
                     " AND CodMeioTransporte = " & ddlMeioTransporte.SelectedValue & _
                     " AND CodDestinacaoItem = " & ddlDestinacaoItem.SelectedValue & _
                     " AND CodigoDaOrigemDoItem = '" & ddlOrigemItem.SelectedValue & "'" & _
                     " AND DataVigencia = " & strDataVigencia & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    CodeExist = True
                    lblMsg.Text = "Modelo Tributário com a Data de Vigência de " & txtDataVigencia.Text & ", já existe no Sistema."
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                CodeExist = True
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try

        End Using

    End Function

    Private Function AddRecord(ByVal strConn As String) As Boolean

        Dim strDataVigencia As String
        Dim nDiaCreated As String
        Dim nMesCreated As String
        Dim nAnoCreated As String
        Dim nLastUpdated As String

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            'Dim dAnoVigente As String
            'Dim i As Long

            Dim theQueryString As String = "SELECT CONVERT(CHAR(10), GETDATE(), 103) AS DATADOSERVIDOR"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                Do While theSqlDataReader.Read()
                    Session("dDTAno") = Trim(theSqlDataReader(0))
                Loop
                theSqlDataReader.Close()
            Catch ex As Exception
                Console.WriteLine(ex.Message)
            End Try

        End Using

        nDiaCreated = Mid(Session("dDTAno"), 1, 2)
        nMesCreated = Mid(Session("dDTAno"), 4, 2)
        nAnoCreated = Mid(Session("dDTAno"), 7, 4)
        nLastUpdated = nDiaCreated & "/" & nMesCreated & "/" & nAnoCreated

        If (IsDate(nLastUpdated)) Then
            nLastUpdated = "'" & FormatMediumDate(nLastUpdated) & "'"
        Else
            nLastUpdated = "Null"
        End If

        AddRecord = False

        strDataVigencia = "'" & FormatMediumDate(txtDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "INSERT INTO ModeloContabilHeader" & _
                     " (" & _
                     " CodigoDaHolding," & _
                     " CodigoDaCia," & _
                     " CodUFOrigem," & _
                     " CodUFDestino," & _
                     " CodNaturezaOperacao," & _
                     " CodCategoriaFiscal," & _
                     " CodMeioTransporte," & _
                     " CodDestinacaoItem," & _
                     " CodigoDaOrigemDoItem," & _
                     " DataVigencia,"

            theQueryString = theQueryString & _
                     " CodTipoTributacaoII," & _
                     " CodTipoTributacaoIPI," & _
                     " CodTipoTributacaoPIS," & _
                     " CodTipoTributacaoCOFINS," & _
                     " CodTipoTributacaoICMS," & _
                     " CodTipoTributacaoICMSST," & _
                     " CreateUser," & _
                     " Created" & _
                     " )"

            theQueryString = theQueryString & _
                     " VALUES" & _
                     " (" & _
                     " '" & ddlHolding.SelectedValue & "'," & _
                     " '" & ddlCompanhia.SelectedValue & "'," & _
                     " " & ddlUFOrigem.SelectedValue & "," & _
                     " " & ddlUFDestino.SelectedValue & "," & _
                     " '" & ddlNaturezaOperacao.SelectedValue & "'," & _
                     " " & ddlCategoriaFiscal.SelectedValue & "," & _
                     " " & ddlMeioTransporte.SelectedValue & "," & _
                     " " & ddlDestinacaoItem.SelectedValue & "," & _
                     " '" & ddlOrigemItem.SelectedValue & "'," & _
                     " " & strDataVigencia & ","

            theQueryString = theQueryString & _
                     " " & ddlTipoTributacaoII.SelectedValue & "," & _
                     " " & ddlTipoTributacaoIPI.SelectedValue & "," & _
                     " " & ddlTipoTributacaoPIS.SelectedValue & "," & _
                     " " & ddlTipoTributacaoCOFINS.SelectedValue & "," & _
                     " " & ddlTipoTributacaoICMS.SelectedValue & "," & _
                     " " & ddlTipoTributacaoICMSST.SelectedValue & "," & _
                     " '" & Session("sNM_USERNICKNAME").ToString & "'," & _
                     " " & nLastUpdated & "" & _
                     " );"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                AddRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Private Function EditRecord(ByVal strConn As String) As Boolean

        'Dim strSQL As String

        'EditRecord = False

        'Using myConnection As New MySqlConnection(strConn)

        '    'strSQL = "UPDATE Item SET" & _
        '    '         " DscItem = '" & Trim(txtDescricaoItem.Text) & "'," & _
        '    '         " DscItemIngles = " & IIf(Trim(txtDescricaoItemIngles.Text) = "", "Null", "'" & Trim(txtDescricaoItemIngles.Text) & "'") & "," & _
        '    '         " DscItemEspanhol = " & IIf(Trim(txtDescricaoItemEspanhol.Text) = "", "Null", "'" & Trim(txtDescricaoItemEspanhol.Text) & "'") & "," & _
        '    '         " CodUnidadeMedidaControle = " & ddlUnidadeControle.SelectedValue & "," & _
        '    '         " CodUnidadeMedidaVenda = " & ddlUnidadeVenda.SelectedValue & "," & _
        '    '         " CodSOSItem = " & IIf(Trim(txtCodigoSOS.Text) = "", "Null", "'" & Trim(txtCodigoSOS.Text) & "'") & "," & _
        '    '         " CodigoDaOrigemDoItem = '" & ddlOrigemItem.SelectedValue & "'," & _
        '    '         " CodDestinacaoItem = " & ddlDestinacaoItem.SelectedValue & "," & _
        '    '         " CodEspecieItem = " & ddlEspecieItem.SelectedValue & "," & _
        '    '         " CodClassificacaoFiscal = " & IIf(Trim(ddlClassificacaoFiscal.Text) = "", "Null", "'" & Trim(ddlClassificacaoFiscal.SelectedValue) & "'") & "," & _
        '    '         " LastUser = '" & Session("strUserID").ToString & "'," & _
        '    '         " LastUpdated = " & "NOW()" & _
        '    '         " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
        '    '         " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
        '    '         " AND CodigoDoEstabelecimento = '" & ddlEstabelecimento.SelectedValue & "'" & _
        '    '         " AND UCASE(CodItem) = '" & UCase(Trim(txtCodigo.Text)) & "';"

        '    Dim myCommand As New MySqlCommand(strSQL, myConnection)

        '    Try
        '        myConnection.Open()
        '        myCommand.ExecuteNonQuery()
        '        myCommand.Connection.Close()
        '        EditRecord = True
        '    Catch ex As Exception
        '        myCommand.Connection.Close()
        '        lblMsg.Text = ex.Message
        '    End Try

        'End Using

    End Function


    Private Function VerifyData(ByVal strConn As String) As Boolean

        Dim strDataVigencia As String

        VerifyData = False

        'strDataVigencia = "'" & FormatMediumDate(txtDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim theQueryString As String
        '    theQueryString = "SELECT CodModTipoTributacaoII" & _
        '             " FROM ModeloTributarioII" & _
        '             " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
        '             " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
        '             " AND CodUFOrigem = " & ddlUFOrigem.SelectedValue & _
        '             " AND CodUFDestino = " & ddlUFDestino.SelectedValue & _
        '             " AND CodNaturezaOperacao = '" & ddlNaturezaOperacao.SelectedValue & "'" & _
        '             " AND CodCategoriaFiscal = '" & ddlCategoriaFiscal.SelectedValue & "'" & _
        '             " AND CodMeioTransporte = '" & ddlMeioTransporte.SelectedValue & "'" & _
        '             " AND CodDestinacaoItem = '" & ddlDestinacaoItem.SelectedValue & "'" & _
        '             " AND CodigoDaOrigemDoItem = '" & ddlOrigemItem.SelectedValue & "'" & _
        '             " AND DataVigencia = " & strDataVigencia & ";"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        If (theSqlDataReader.Read()) Then
        '            VerifyData = True
        '            lblMsg.Text = "Exclusão não permitida, existe relação de Modelo Tributário com dados de linha."
        '        End If
        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        VerifyData = True
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = ex.Message
        '    End Try

        'End Using

    End Function

    Private Function DeleteRecord(ByVal strConn As String) As Boolean

        Dim strDataVigencia As String
        Dim strSQL As String

        DeleteRecord = False

        strDataVigencia = "'" & FormatMediumDate(txtDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String
            theQueryString = "DELETE FROM ModeloTributarioHeader" & _
                     " WHERE CodigoDaHolding = '" & ddlHolding.SelectedValue & "'" & _
                     " AND CodigoDaCia = '" & ddlCompanhia.SelectedValue & "'" & _
                     " AND CodUFOrigem = " & ddlUFOrigem.SelectedValue & _
                     " AND CodUFDestino = " & ddlUFDestino.SelectedValue & _
                     " AND CodNaturezaOperacao = '" & ddlNaturezaOperacao.SelectedValue & "'" & _
                     " AND CodCategoriaFiscal = '" & ddlCategoriaFiscal.SelectedValue & "'" & _
                     " AND CodMeioTransporte = '" & ddlMeioTransporte.SelectedValue & "'" & _
                     " AND CodDestinacaoItem = '" & ddlDestinacaoItem.SelectedValue & "'" & _
                     " AND CodigoDaOrigemDoItem = '" & ddlOrigemItem.SelectedValue & "'" & _
                     " AND DataVigencia = " & strDataVigencia & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                theSqlDataReader.Close()
                DeleteRecord = True
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Function

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click

        lblMsg.ForeColor = Drawing.Color.Blue
        lblMsg.Text = ""

        Session.Add("blBack", True)

        Response.Redirect("ModeloContabil.aspx")

    End Sub


    Protected Sub btnII_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnII.Click

        Session.Add("strCodigoDaHolding", ddlHolding.SelectedValue)
        Session.Add("strHolding", ddlHolding.SelectedItem)
        Session.Add("strCodigoDaCia", ddlCompanhia.SelectedValue)
        Session.Add("strCia", ddlCompanhia.SelectedItem)
        Session.Add("CodUFOrigem", ddlUFOrigem.SelectedValue)
        Session.Add("strUFOrigem", ddlUFOrigem.SelectedItem)
        Session.Add("CodUFDestino", ddlUFDestino.SelectedValue)
        Session.Add("strUFDestino", ddlUFDestino.SelectedItem)
        Session.Add("strCodNaturezaOperacao", ddlNaturezaOperacao.SelectedValue)
        Session.Add("strNaturezaOperacao", ddlNaturezaOperacao.SelectedItem)
        Session.Add("CodCategoriaFiscal", ddlCategoriaFiscal.SelectedValue)
        Session.Add("strCategoriaFiscal", ddlCategoriaFiscal.SelectedItem)
        Session.Add("CodMeioTransporte", ddlMeioTransporte.SelectedValue)
        Session.Add("strMeioTransporte", ddlMeioTransporte.SelectedItem)
        Session.Add("strCodigoDaOrigemDoItem", ddlOrigemItem.SelectedValue)
        Session.Add("strOrigemDoItem", ddlOrigemItem.SelectedItem)
        Session.Add("CodDestinacaoItem", ddlDestinacaoItem.SelectedValue)
        Session.Add("strDestinacaoItem", ddlDestinacaoItem.SelectedItem)
        Session.Add("strDataVigencia", txtDataVigencia.Text)

        Session.Add("CodTipoTributacao", ddlTipoTributacaoII.SelectedValue)
        Session.Add("strTipoTributacaoII", ddlTipoTributacaoII.SelectedItem)

        Response.Redirect("ModeloTributarioDataII.aspx")

    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

End Class
